package com.lagou.dao.impl;

import com.lagou.dao.CourseContentDao;
import com.lagou.pojo.Course;
import com.lagou.pojo.Course_Lesson;
import com.lagou.pojo.Course_Section;
import com.lagou.utils.DateUtils;
import com.lagou.utils.DruidUtils;
import org.apache.commons.dbutils.QueryLoader;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.SQLException;
import java.util.List;

/**
 * @author TianSu
 * @creat 2021-05-11-16:01
 **/
public class CourseContentDaoImpl implements CourseContentDao {
    @Override
    public Course findCourseByCourseId(int courseId) {
        try {
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
            String sql = "SELECT id,course_name FROM course WHERE id=?";
            Course course = qr.query(sql, new BeanHandler<Course>(Course.class), courseId);
            return course;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    //保存章节信息
    public int saveSection(Course_Section section) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "INSERT INTO course_section( \n" +
                    "course_id ,\n" +
                    "section_name, \n" +
                    "description, \n" +
                    "order_num, \n" +
                    "STATUS, \n" +
                    "create_time, \n" +
                    "update_time)VALUES(?,?,?,?,?,?,?);";
            Object[] param = {section.getCourse_id(),section.getSection_name(),section.getDescription(),section.getOrder_num(),section.getStatus(),section.getCreate_time(),section.getUpdate_time()};
            int row = qr.update(sql,param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    //根据课程查看章节
    @Override
    public List<Course_Section> findSectionAndLessonByCourseId(int courseId) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "SELECT \n" +
                    "id, \n" +
                    "course_id ,\n" +
                    "section_name, \n" +
                    "description, \n" +
                    "order_num, \n" +
                    "STATUS \n" +
                    "FROM course_section WHERE course_id = ? \n";

            List<Course_Section> sectionList = qr.query(sql, new BeanListHandler<Course_Section>(Course_Section.class), courseId);

            //根据章节id查询课时信息
            for (Course_Section section:sectionList){
                //调用方法，获取章节对应的课时
                List<Course_Lesson> lessonList = findLessonBySectionId(section.getId());

                //将课时数据封装到章节对象中
                section.setLessonList(lessonList);
            }
            return sectionList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    @Override
    //根据章节查看课时
    public List<Course_Lesson> findLessonBySectionId(int sectionId) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "SELECT \n" +
                    "id, \n" +
                    "course_id ,\n" +
                    "section_id, \n" +
                    "theme, \n" +
                    "duration, \n" +
                    "is_free, \n" +
                    "order_num, \n" +
                    "STATUS \n" +
                    "FROM course_lesson WHERE section_id = ? \n";

            List<Course_Lesson> lessonList = qr.query(sql, new BeanListHandler<Course_Lesson>(Course_Lesson.class), sectionId);
            return lessonList;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    //修改章节
    public int updateSection(Course_Section section){
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "UPDATE course_section SET \n" +
                "section_name=?, \n" +
                "description=?, \n" +
                "order_num=?, \n" +
                "update_time=?  WHERE id = ? ";
            Object[] param = {section.getSection_name(),section.getDescription(),
                    section.getOrder_num(),section.getUpdate_time(),section.getId()};
            int row = qr.update(sql,param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }


    @Override
    //修改章节状态
    public int updateSectionStatus(int id, int status) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "UPDATE course_section SET \n" +
                    "STATUS=?, \n" +
                    "update_time=?  WHERE id = ? ";
            Object[] param = {status, DateUtils.getDateFormart(),id};
            int row = qr.update(sql,param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int saveLesson(Course_Lesson lesson) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "INSERT INTO course_lesson( \n" +
                    "course_id ,\n" +
                    "section_id, \n" +
                    "theme, \n" +
                    "duration, \n" +
                    "is_free, \n" +
                    "order_num)VALUES(?,?,?,?,?,?);";
            Object[] param = {lesson.getCourse_id(),lesson.getSection_id(), lesson.getTheme(),
                    lesson.getDuration(),lesson.getIs_free(), lesson.getOrderNum()};
            int row = qr.update(sql,param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }

    @Override
    public int updateLesson(Course_Lesson lesson) {
        try {
            //1、创建QueryRunner
            QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());

            //2、编写sql
            String sql = "UPDATE course_lesson SET \n" +
                    "course_id=? ,\n" +
                    "section_id=?, \n" +
                    "theme=?, \n" +
                    "duration=?, \n" +
                    "is_free=?, \n" +
                    "order_num=? WHERE id = ? ";
            Object[] param = {lesson.getCourse_id(),lesson.getSection_id(), lesson.getTheme(),
                    lesson.getDuration(),lesson.getIs_free(), lesson.getOrderNum(),lesson.getId()};
            int row = qr.update(sql,param);
            return row;
        } catch (SQLException e) {
            e.printStackTrace();
            return 0;
        }
    }
}
